﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaintenanceDataFileSize]
#-- Purpose:		Maintains the amount of free space that needs to be 
#--					retained for controlled file growth and reduced 
#--					fragmentation
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaintenanceDataFileSize]
(
	@iIncrement				int		= 50, 
	@iMaxFullPct			int		= 90,
	@min_disk_free_pct		int		= 15,
	@debug_only				bit		= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@database_name sysname, @sSQL varchar(MAX), @vlf_optimal int, @vlf_minimal int,
			@pct_log_total float, @pct_log_used float
DECLARE		@diskspace TABLE (dbd_drive nvarchar(256), dbd_capacityMB varchar(256), dbd_usedMB varchar(256), dbd_freeMB varchar(256), dbd_free_pct varchar(6))
DECLARE		@file_size TABLE (database_id int, Fileid int, FileGroup int, TotalExtents int, UsedExtents int, name sysname, physical_name nchar(520))
DECLARE		@progname sysname
DECLARE	@results TABLE
(
	database_id				int,
	database_name			sysname,
	Fileid					int,
	FileGroup				int,
	total_MB				decimal(18,2),
	used_MB					decimal(18,2),
	free_MB					decimal(18,2),
	newSize_MB				bigint,
	name					sysname,
	physical_name			nvarchar(2000)
)

--- Define the name of this program (for logs)
SELECT		@progname = 'spMaintenanceDataFileSize'

--- Get Current Disk Information
INSERT INTO @diskspace (dbd_drive, dbd_capacityMB, dbd_usedMB, dbd_freeMB, dbd_free_pct) EXEC dbo.GetVolumeSpace ''

--- Retrieve the size of the data files and database log file info
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT		db.name
    FROM		master.sys.databases db
	WHERE		db.name NOT IN ('tempdb', 'ReportServerTempDB', 'model')
				AND db.name NOT IN (SELECT database_name FROM dbo.vwDatabase_DoNotMaintain)
				AND state = 0
				AND source_database_id IS NULL  -- Cannot change file sizes on Snapshots
	ORDER BY	db.database_id
OPEN crs
FETCH NEXT FROM crs INTO @database_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Retrieve the size of the data files
	SELECT	@sSQL = 'USE ' + QUOTENAME(@database_name) + '; DBCC SHOWFILESTATS WITH NO_INFOMSGS;'
	INSERT INTO @file_size (Fileid, FileGroup, TotalExtents, UsedExtents, name, physical_name) 
		EXEC (@sSQL)
	UPDATE @file_size SET database_id = DB_ID(@database_name) WHERE database_id IS NULL
	
	FETCH NEXT FROM crs INTO @database_name
  END
CLOSE crs
DEALLOCATE crs

--- Get the list of files that need to grow
;WITH data_size AS
(
	SELECT		t1.database_id,
				DB_NAME(t1.database_id) database_name,
				t1.Fileid,
				t1.FileGroup,
				((t1.TotalExtents * 8.00 / 1024) * (mf.size / t1.TotalExtents)) TotalMB,
				((t1.UsedExtents * 8.00 / 1024) * (mf.size / t1.TotalExtents)) UsedMB,
				(((t1.TotalExtents - t1.UsedExtents) * 8.00 / 1024) * (mf.size / t1.TotalExtents)) FreeMB,
				t1.name,
				t1.physical_name
	FROM		sys.master_files mf
	JOIN		@file_size t1 ON mf.name = t1.name
				AND mf.physical_name = t1.physical_name
)
INSERT INTO @results
	SELECT		database_id,
				database_name,
				Fileid,
				FileGroup,
				TotalMB,
				UsedMB,
				FreeMB,
				(CAST(LEFT(CAST(CAST((UsedMB / 0.85) as int) as varchar(1000)), 1) as int) + 1) * POWER(10, LEN(CAST((UsedMB / 0.85) as int)) - 1) newTotalMB,
				name,
				physical_name
	FROM		data_size
	WHERE		(UsedMB / (TotalMB * 1.00)) >= (@iMaxFullPct / 100.00)

--- If debug_only, display the files needing growth
IF @debug_only = 1
  BEGIN
	SELECT		t2.dbd_capacityMB,
				t2.dbd_freeMB,
				t2.growth_MB,
				t1.*
	FROM		@results t1
	JOIN		(
				SELECT		dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct,
							SUM(newSize_MB - total_MB) growth_MB
				FROM		@results res
				JOIN		@diskspace dbd ON res.physical_name LIKE dbd.dbd_drive + '%'
				WHERE		(res.used_MB / (res.total_MB * 1.00)) >= (@iMaxFullPct / 100.00)
				GROUP BY	dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct
				HAVING		((dbd.dbd_freeMB - SUM(((CAST(LEFT(CAST(CAST((res.used_MB / 0.85) as int) as varchar(1000)), 1) as int) + 1) * POWER(10, LEN(CAST((res.used_MB / 0.85) as int)) - 1)) - res.total_MB) ) / CAST(dbd.dbd_capacityMB as float)) > (@min_disk_free_pct / 100.0)
				) t2 ON t1.physical_name LIKE t2.dbd_drive + '%'
  END

--- Make the Changes to data size
DECLARE crs CURSOR FOR
	SELECT		'ALTER DATABASE ' + t1.database_name + ' MODIFY FILE ( NAME = N''' + t1.name + ''', SIZE = ' + CAST(t1.newSize_MB as varchar(1000)) + 'MB )'
	FROM		@results t1
	JOIN		(
				SELECT		dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct,
							SUM(newSize_MB - total_MB) growth_MB
				FROM		@results res
				JOIN		@diskspace dbd ON res.physical_name LIKE dbd.dbd_drive + '%'
				WHERE		(res.used_MB / (res.total_MB * 1.00)) >= (@iMaxFullPct / 100.00)
				GROUP BY	dbd.dbd_drive,
							dbd.dbd_capacityMB,
							dbd.dbd_usedMB,
							dbd.dbd_freeMB,
							dbd.dbd_free_pct
				HAVING		((dbd.dbd_freeMB - SUM(((CAST(LEFT(CAST(CAST((res.used_MB / 0.85) as int) as varchar(1000)), 1) as int) + 1) * POWER(10, LEN(CAST((res.used_MB / 0.85) as int)) - 1)) - res.total_MB) ) / CAST(dbd.dbd_capacityMB as float)) > (@min_disk_free_pct / 100.0)
				) t2 ON t1.physical_name LIKE t2.dbd_drive + '%'
OPEN crs
FETCH NEXT FROM crs INTO @sSQL
WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF @debug_only = 1
		PRINT @sSQL
	ELSE
	  BEGIN
		EXEC (@sSQL)
		
		--- Log to the SQL Server error log
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL
	  END
	  
	FETCH NEXT FROM crs INTO @sSQL
  END
CLOSE crs
DEALLOCATE crs
  		
SET NOCOUNT OFF